package models

import play.api.db.slick.Config.driver.simple._

case class Group(id: Option[Long], name: String, homePage: Option[String])

object GroupTbl extends Table[Group]("RES_GRP") {

  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name", O.NotNull)
  def homePage = column[String]("home_pg", O.Nullable)
  def uniqueName = index("idx_grp_name", name, unique = true)

  def * = id.? ~ name ~ homePage.? <> (Group.apply _, Group.unapply _)

  def autoInc = * returning id

  val byId = createFinderBy(_.id)
}

object GroupDAO {

 /**
	* Retrieve a group from the id
	* @param id
	*/
	def findById(id: Long)(implicit s: Session): Option[Group] =
	GroupTbl.byId(id).firstOption

 /**
	* Count all groups
	*/
	def count(implicit s: Session): Int =
	Query(GroupTbl.length).first

  /**
   * Insert a new group
   * @param group
   */
  def insert(group: Group)(implicit s: Session) {
    GroupTbl.autoInc.insert(group)
  }

  /**
   * Update a group
   * @param group
   */
  def update(group: Group)(implicit s: Session) {
    val groupToUpdate: Group = group.copy(group.id)
    GroupTbl.where(_.id === group.id).update(groupToUpdate)
  }

  /**
   * Delete a group
   * @param id
   */
  def delete(id: Long)(implicit s: Session) {
    GroupTbl.where(_.id === id).delete
  }

  def list()(implicit s: Session): List[Group] = {
    val query = Query(GroupTbl).sortBy(_.name)
    query.list
  }

  def listWithResources(filter: String = "%")(implicit s: Session): List[(Group, Resource)] = {
    val query =
      (for {
        (group, resource) <- GroupTbl rightJoin ResourceTbl on (_.id === _.groupId)
        if ((group.name.toLowerCase like filter.toLowerCase) ||
            (resource.name.toLowerCase like filter.toLowerCase))
      } yield (group, resource))

    val result = query.list  // returns: List[(Group, Resource)]
    
    result
  }

  /**
   * Construct the Map[String,String] needed to fill a select options set
   */
  def options(implicit s: Session): Seq[(String, String)] = {
    val query = (for {
      group <- GroupTbl
    } yield (group.id, group.name)).sortBy(_._2)
    query.list.map(row => (row._1.toString, row._2))
  }

}
